Imports System
Imports System.Web.Profile
Imports System.Web
Imports System.Web.Configuration
Imports System.Security.Principal
Imports System.Security.Permissions
Imports System.Globalization
Imports System.Runtime.Serialization
Imports System.Collections
Imports System.Collections.Specialized
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports System.Runtime.Serialization.Formatters.Binary
Imports System.IO
Imports System.Reflection
Imports System.Xml.Serialization
Imports System.Text
Imports System.Configuration.Provider
Imports System.Configuration
Imports System.Web.Hosting
Imports System.Web.Util
Imports System.Security.Cryptography
Imports nsDataProvider = KDESolutions.Providers.DataProvider

Public Class DBProfileProvider
    Inherits ProfileProvider

    Private _ApplicationName As String
    Private _SchemaVersionCheck As Integer
    Private _AppId As String
    Private _TablePrefix As String
    Private _DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString As String

#Region "Public Properties"
    Public Overrides Property ApplicationName() As String
        Get
            Return _ApplicationName
        End Get
        Set(ByVal value As String)
            If (value.Length > 256) Then
                Throw New ProviderException("Provider application name too long")
            End If
            _ApplicationName = value
        End Set
    End Property

    Public Property MyDBType() As String
        Get
            Return _DBType
        End Get
        Set(ByVal value As String)
            _DBType = value
        End Set
    End Property
#End Region
    Public Overrides Sub Initialize(ByVal name As String, ByVal config As NameValueCollection)
        ' Remove CAS in sample: HttpRuntime.CheckAspNetHostingPermission(AspNetHostingPermissionLevel.Low, SR.Feature_not_supported_at_this_level);
        If (config Is Nothing) Then
            Throw New ArgumentNullException("config")
        End If
        '***************************************************************************
        '***************************************************************************
        '***************************************************************************
        'Begin of product registration
        Dim productKey As String = String.Empty
        _TablePrefix = GetConfigValue(config("tablePrefix"), "aspnet")
        Try
            If (config("productKey") Is Nothing) Then
                Dim newEx As New Exception("You must supply a product key!")
                Throw newEx
            ElseIf (config("activationDate") Is Nothing) Then
                Dim newEx As New Exception("You must supply an order date.")
                Throw newEx
            End If

            Dim tempProductKey As String = String.Empty
            Dim tempProductKey1 As String = String.Empty
            Dim productName As String = Me.GetType.Assembly.GetName.ToString.Split(",")(0)
            Dim productVersion As String = Me.GetType.Assembly.GetName.Version.ToString
            Dim orderDate As String = CType(config("activationDate"), Date).ToShortDateString
            'Encrypt the password
            Dim md5Hasher As New MD5CryptoServiceProvider()

            Dim hashedBytes As Byte()
            Dim encoder As New UTF8Encoding()
            tempProductKey = productName & productVersion & orderDate

            hashedBytes = md5Hasher.ComputeHash(encoder.GetBytes(tempProductKey))
            For Each i As String In hashedBytes
                tempProductKey1 = tempProductKey1 & i & "-"
            Next
            tempProductKey1 = tempProductKey1.Substring(0, (tempProductKey1.Length - 1))

            productKey = tempProductKey1.Split("-")(0) & tempProductKey1.Split("-")(3) & "-" & _
            tempProductKey1.Split("-")(5) & tempProductKey1.Split("-")(2) & "-" & tempProductKey1.Split("-")(7) & tempProductKey1.Split("-")(9) & _
            "-" & tempProductKey1.Split("-")(1) & tempProductKey1.Split("-")(4)

        Catch
            Throw New HttpException("Error validating your product key, please ensure the information" & _
            " in your web.config file is correct and parameters are case sensitive.")
        End Try

        If (config("productKey") = productKey) = False Then
            Throw New HttpException("Invalid Product Key!")
        End If
        'End of product registration
        '************************************************************************
        '***************************************************************************
        '***************************************************************************
        If String.IsNullOrEmpty(config("dataProviderGroup")) Then
            Throw New HttpException("You must provide a dataProviderGroup")
        Else
            Dim DataProviderGroup As KDESolutions.Providers.DataProvider.DataProviderConfiguration = _
            CType(ConfigurationManager.GetSection(config("dataProviderGroup")), KDESolutions.Providers.DataProvider.DataProviderConfiguration)
            If String.IsNullOrEmpty(DataProviderGroup.DBType) = False Then
                _DBType = DataProviderGroup.DBType
                Me.MyDBType = DataProviderGroup.DBType
            End If
            If String.IsNullOrEmpty(DataProviderGroup.DBServer) = False Then
                _DBServer = DataProviderGroup.DBServer
            End If
            If String.IsNullOrEmpty(DataProviderGroup.DBUser) = False Then
                _DBUser = DataProviderGroup.DBUser
            End If
            If String.IsNullOrEmpty(DataProviderGroup.DBPassword) = False Then
                _DBPassword = DataProviderGroup.DBPassword
            End If
            If String.IsNullOrEmpty(DataProviderGroup.DBDatabaseName) = False Then
                _DBDatabaseName = DataProviderGroup.DBDatabaseName
            End If
            If String.IsNullOrEmpty(DataProviderGroup.ConnectString) = False Then
                _ConnectString = DataProviderGroup.ConnectString
            End If
        End If


        If ((name Is Nothing) _
                    OrElse (name.Length < 1)) Then
            name = "DBProfileProvider"
        End If
        If String.IsNullOrEmpty(config("description")) Then
            config.Remove("description")
            config.Add("description", "ProfileDBProvider")
        End If
        MyBase.Initialize(name, config)
        _SchemaVersionCheck = 0
        _ApplicationName = config("applicationName")
        If String.IsNullOrEmpty(_ApplicationName) Then
            _ApplicationName = GetDefaultAppName()
        End If
        If (_ApplicationName.Length > 255) Then
            Throw New ProviderException("Provider application name too long")
        End If
        _AppId = GetAppId()
        config.Remove("commandTimeout")
        config.Remove("connectionStringName")
        config.Remove("applicationName")
        config.Remove("dataProviderGroup")
        config.Remove("productKey")
        config.Remove("activationDate")
        config.Remove("tablePrefix")
        If (config.Count > 0) Then
            Dim attribUnrecognized As String = config.GetKey(0)
            If Not String.IsNullOrEmpty(attribUnrecognized) Then
                Throw New ProviderException("Provider unrecognized attribute, " & attribUnrecognized)
            End If
        End If
    End Sub

    Public Overrides Function GetPropertyValues(ByVal sc As SettingsContext, ByVal properties As SettingsPropertyCollection) As SettingsPropertyValueCollection
        Dim svc As SettingsPropertyValueCollection = New SettingsPropertyValueCollection
        If (properties.Count < 1) Then
            Return svc
        End If
        Dim username As String = CType(sc("UserName"), String)
        For Each prop As SettingsProperty In properties
            If (prop.SerializeAs = SettingsSerializeAs.ProviderSpecific) Then
                If (prop.PropertyType.IsPrimitive OrElse (prop.PropertyType Is GetType(System.String))) Then
                    prop.SerializeAs = SettingsSerializeAs.String
                Else
                    prop.SerializeAs = SettingsSerializeAs.Xml
                End If
            End If
            svc.Add(New SettingsPropertyValue(prop))
        Next
        If Not String.IsNullOrEmpty(username) Then
            GetPropertyValuesFromDatabase(username, svc)
        End If
        Return svc
    End Function

    Private Sub GetPropertyValuesFromDatabase(ByVal userName As String, ByVal svc As SettingsPropertyValueCollection)
        ' Comment out events in sample: if (HostingEnvironment.IsHosted && EtwTrace.IsTraceEnabled(EtwTraceLevel.Information, EtwTraceFlags.AppSvc)) EtwTrace.Trace(EtwTraceType.ETW_TYPE_PROFILE_BEGIN, HttpContext.Current.WorkerRequest);
        Dim context As HttpContext = HttpContext.Current
        Dim names() As String = Nothing
        Dim values As String = Nothing
        Dim buf() As Byte = Nothing
        Dim sName As String = Nothing
        If (Not (context) Is Nothing) Then
            sName = context.Request.IsAuthenticated
        End If
        Try
            Dim result As New Generic.List(Of Common.DbDataRecord)
            CheckSchemaVersion()
            result = Profile_GetProperties(userName, DateTime.UtcNow)
            For Each reader As Common.DbDataRecord In result
                names = reader.GetString(0).Split(Microsoft.VisualBasic.ChrW(58))
                values = reader.GetString(1)
                Dim size As Integer = CType(reader.GetBytes(2, 0, Nothing, 0, 0), Integer)
                buf = New Byte((size) - 1) {}
                reader.GetBytes(2, 0, buf, 0, size)
            Next
            ParseDataFromDB(names, values, buf, svc)
            ' Comment out events in sample: if (HostingEnvironment.IsHosted && EtwTrace.IsTraceEnabled(EtwTraceLevel.Information, EtwTraceFlags.AppSvc)) EtwTrace.Trace(EtwTraceType.ETW_TYPE_PROFILE_END, HttpContext.Current.WorkerRequest, userName);
        Catch Ex As System.Exception
            Throw
        End Try
    End Sub

    Public Overrides Sub SetPropertyValues(ByVal sc As SettingsContext, ByVal properties As SettingsPropertyValueCollection)
        Dim username As String = CType(sc("UserName"), String)
        Dim userIsAuthenticated As Boolean = CType(sc("IsAuthenticated"), Boolean)
        If ((username Is Nothing) _
                    OrElse ((username.Length < 1) _
                    OrElse (properties.Count < 1))) Then
            Return
        End If
        Dim names As String = String.Empty
        Dim values As String = String.Empty
        Dim buf() As Byte = Nothing
        PrepareDataForSaving(names, values, buf, True, properties, userIsAuthenticated)
        If (names.Length = 0) Then
            Return
        End If
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Try
            CheckSchemaVersion()
            Dim sSQL As String
            Dim UserId As String = String.Empty
            sSQL = "SELECT UserId " & _
            "FROM " & _TablePrefix & "_Users " & _
            "WHERE  ApplicationId = ? AND LoweredUserName = ?"
            DAP.AddParameter("@ApplicationId", _AppId, DbType.String)
            DAP.AddParameter("@LoweredUserName", username.ToLower, DbType.String)
            UserId = DAP.ExecuteScalar(sSQL)
            If String.IsNullOrEmpty(UserId) Then
                Dim newUserId As String = System.Guid.NewGuid.ToString
                sSQL = "INSERT INTO " & _TablePrefix & "_Users(ApplicationId, UserId, UserName, " & _
                "LoweredUserName, IsAnonymous, LastActivityDate) " & _
                "VALUES(?, ?, ?, ?, ?, ?)"
                DAP.AddParameter("@ApplicationId", _AppId, DbType.String)
                DAP.AddParameter("@UserId", newUserId, DbType.String)
                DAP.AddParameter("@UserName", username, DbType.String)
                DAP.AddParameter("@LoweredUserName", username.ToLower, DbType.String)
                DAP.AddParameter("@IsAnonymous", 1, DbType.Int16)
                DAP.AddParameter("@LastActivityDate", DateTime.UtcNow, DbType.DateTime)
                If DAP.ExecuteNonQuery(sSQL) = 1 Then
                    UserId = newUserId
                End If

                If String.IsNullOrEmpty(UserId) Then
                    Throw New ProviderException("Error finding or adding user")
                End If
            End If
            sSQL = "SELECT Count(*) As ResultCount " & _
            "FROM " & _TablePrefix & "_Profile " & _
            "WHERE UserId = ?"
            DAP.AddParameter("@UserId", UserId, DbType.String)
            Dim resultCount As Integer = DAP.ExecuteScalar(sSQL)
            Select Case resultCount
                Case 0
                    sSQL = "INSERT INTO " & _TablePrefix & "_Profile(PropertyNames, " & _
                    "PropertyValuesString, PropertyValuesBinary, LastUpdatedDate, UserId) " & _
                    "VALUES (?, ?, ?, ?, ?)"
                Case 1
                    sSQL = "UPDATE " & _TablePrefix & "_Profile " & _
                    "SET PropertyNames = ?, PropertyValuesString = ?, " & _
                    "PropertyValuesBinary = ?, LastUpdatedDate = ? " & _
                    "WHERE  UserId = ?"
                Case Else
                    Throw New ProviderException("User, " & username & " exists more than once in the profile table.")
            End Select
            DAP.AddParameter("@PropertyNames", names, DbType.String)
            DAP.AddParameter("@PropertyValuesString", values, DbType.String)
            DAP.AddParameter("@PropertyValuesBinary", Convert.ToBase64String(buf), DbType.Object)
            'DAP.AddParameter("@IsUserAnonymous", (Not userIsAuthenticated), DbType.Int16)
            DAP.AddParameter("@LastUpdatedDate", DateTime.UtcNow, DbType.DateTime)
            DAP.AddParameter("@UserId", UserId, DbType.String)
            DAP.ExecuteNonQuery(sSQL)
        Catch Ex As System.Exception
            Throw
        Finally
            DAP.Dispose()
        End Try
    End Sub

    Public Overloads Overrides Function DeleteProfiles(ByVal profiles As ProfileInfoCollection) As Integer
        If (profiles Is Nothing) Then
            Throw New ArgumentNullException("profiles")
        End If
        If (profiles.Count < 1) Then
            Throw New ArgumentException("Parameter collection empty, " & profiles.ToString)
        End If
        Dim usernames() As String = New String((profiles.Count) - 1) {}
        Dim iter As Integer = 0
        For Each profile As ProfileInfo In profiles
            usernames(iter) = profile.UserName
            iter = iter + 1
        Next
        Return DeleteProfiles(usernames)
    End Function

    Public Overloads Overrides Function DeleteProfiles(ByVal usernames() As String) As Integer
        CheckArrayParameter(usernames, True, True, True, 255, "usernames")
        Dim numProfilesDeleted As Integer = 0
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Dim beginTranCalled As Boolean = False
        Try
            Dim sSQL As String
            CheckSchemaVersion()
            Dim numUsersRemaing As Integer = usernames.Length
            For Each username As String In usernames
                Dim sUserId As String = String.Empty
                sSQL = "SELECT UserId FROM " & _
                _TablePrefix & "_Users " & _
                "WHERE LoweredUserName = ? AND ApplicationId = ?"
                DAP.AddParameter("@LoweredUserName", username.ToLower, DbType.String)
                DAP.AddParameter("@Applicationid", _AppId, DbType.String)
                sUserId = DAP.ExecuteScalar(sSQL)
                If String.IsNullOrEmpty(sUserId) Then
                    Throw New ProviderException("User not found, " & username)
                End If
                sSQL = "DELETE FROM " & _TablePrefix & "_Profile WHERE UserId = ?"
                DAP.AddParameter("@UserId", sUserId, DbType.String)
                Dim o As Object = DAP.ExecuteNonQuery(sSQL)
                If (TypeOf ((Not (o) Is Nothing) AndAlso o) Is Integer) Then
                    numProfilesDeleted = (numProfilesDeleted + CType(o, Integer))
                End If
            Next
        Catch Ex As System.Exception
            Throw
        Finally
            DAP.Dispose()
        End Try
        Return numProfilesDeleted
    End Function

    Public Overrides Function DeleteInactiveProfiles(ByVal authenticationOption As ProfileAuthenticationOption, ByVal userInactiveSinceDate As DateTime) As Integer
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Try
            CheckSchemaVersion()
            Dim deleteAnonUsers As Boolean = False
            Dim sSQL As String = "DELETE FROM " & _TablePrefix & "_Profile " & _
            "WHERE UserId IN (SELECT UserId FROM " & _TablePrefix & "_Users u " & _
            "WHERE ApplicationId = ? AND LastActivityDate <= ? "
            Select Case authenticationOption
                Case ProfileAuthenticationOption.All
                    sSQL = sSQL & ")"
                    deleteAnonUsers = True
                Case ProfileAuthenticationOption.Anonymous
                    sSQL = sSQL & "AND IsAnonymous = ?)"
                    deleteAnonUsers = True
                Case ProfileAuthenticationOption.Authenticated
                    sSQL = sSQL & "AND IsAnonymous = ?)"
                    deleteAnonUsers = False
            End Select
            DAP.AddParameter("@ApplicationId", _AppId, DbType.String)
            DAP.AddParameter("@LastActivityDate", userInactiveSinceDate.ToUniversalTime, DbType.DateTime)
            If authenticationOption <> ProfileAuthenticationOption.All Then
                DAP.AddParameter("@IsAnonymous", deleteAnonUsers, DbType.Int16)
            End If
            Dim o As Object = DAP.ExecuteScalar(sSQL)
            If ((o Is Nothing) OrElse Not (TypeOf o Is Integer)) Then
                Return 0
            End If
            Return CType(o, Integer)
        Catch Ex As System.Exception
            Throw
        Finally
            DAP.Dispose()
        End Try
    End Function

    Public Overrides Function GetNumberOfInactiveProfiles(ByVal authenticationOption As ProfileAuthenticationOption, ByVal userInactiveSinceDate As DateTime) As Integer
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Try
            CheckSchemaVersion()
            Dim deleteAnonUsers As Boolean = False
            Dim sSQL As String
            sSQL = "SELECT  COUNT(*) As InactiveCount  FROM " & _
            _TablePrefix & "_Users u, " & _TablePrefix & "_Profile p " & _
            "WHERE ApplicationId = ? AND u.UserId = p.UserId " & _
            "AND LastActivityDate <= ? "
            Select Case authenticationOption
                Case ProfileAuthenticationOption.All
                    sSQL = sSQL & ")"
                    deleteAnonUsers = True
                Case ProfileAuthenticationOption.Anonymous
                    sSQL = sSQL & "AND IsAnonymous = ?)"
                    deleteAnonUsers = True
                Case ProfileAuthenticationOption.Authenticated
                    sSQL = sSQL & "AND IsAnonymous = ?)"
                    deleteAnonUsers = False
            End Select
            DAP.AddParameter("@ApplicationId", _AppId, DbType.String)
            DAP.AddParameter("@LastActivityDate", userInactiveSinceDate.ToUniversalTime, DbType.DateTime)
            If authenticationOption <> ProfileAuthenticationOption.All Then
                DAP.AddParameter("@IsAnonymous", deleteAnonUsers, DbType.Int16)
            End If
            Dim o As Object = DAP.ExecuteScalar(sSQL)
            If ((o Is Nothing) _
                        OrElse Not (TypeOf o Is Integer)) Then
                Return 0
            End If
            Return CType(o, Integer)
        Catch Ex As System.Exception
            Throw
        Finally
            DAP.Dispose()
        End Try
    End Function

    Public Overrides Function GetAllProfiles(ByVal authenticationOption As ProfileAuthenticationOption, ByVal pageIndex As Integer, ByVal pageSize As Integer, ByRef totalRecords As Integer) As ProfileInfoCollection
        Return GetProfilesForQuery(authenticationOption, pageIndex, pageSize, totalRecords)
    End Function

    Public Overrides Function GetAllInactiveProfiles(ByVal authenticationOption As ProfileAuthenticationOption, ByVal userInactiveSinceDate As DateTime, ByVal pageIndex As Integer, ByVal pageSize As Integer, ByRef totalRecords As Integer) As ProfileInfoCollection
        Return GetProfilesForQuery(authenticationOption, pageIndex, pageSize, totalRecords, userInactiveSinceDate.ToUniversalTime)
    End Function

    Public Overrides Function FindProfilesByUserName(ByVal authenticationOption As ProfileAuthenticationOption, ByVal usernameToMatch As String, ByVal pageIndex As Integer, ByVal pageSize As Integer, ByRef totalRecords As Integer) As ProfileInfoCollection
        CheckParameter(usernameToMatch, True, True, False, 255, "username")
        Return GetProfilesForQuery(authenticationOption, pageIndex, pageSize, totalRecords, , usernameToMatch)
    End Function

    Public Overrides Function FindInactiveProfilesByUserName(ByVal authenticationOption As ProfileAuthenticationOption, ByVal usernameToMatch As String, ByVal userInactiveSinceDate As DateTime, ByVal pageIndex As Integer, ByVal pageSize As Integer, ByRef totalRecords As Integer) As ProfileInfoCollection
        Return GetProfilesForQuery(authenticationOption, pageIndex, pageSize, totalRecords, userInactiveSinceDate.ToUniversalTime, usernameToMatch)
    End Function

    ' Remove CAS in sample: [SecurityPermission(SecurityAction.Assert, Flags = SecurityPermissionFlag.SerializationFormatter)]
    Private Shared Sub ParseDataFromDB(ByVal names() As String, ByVal values As String, ByVal buf() As Byte, ByVal properties As SettingsPropertyValueCollection)
        If ((names Is Nothing) OrElse ((values Is Nothing) _
                    OrElse ((buf Is Nothing) OrElse (properties Is Nothing)))) Then
            Exit Sub
        End If
        Try
            Dim iter As Integer = 0
            For Each stringItem As String In names
                If (iter < (names.Length / 4)) Then
                    Dim name As String = names((iter * 4))
                    Dim pp As SettingsPropertyValue = properties(name)
                    If (Not pp Is Nothing) Then
                        Dim startPos As Integer = Int32.Parse(names(((iter * 4) + 2)), CultureInfo.InvariantCulture)
                        Dim length As Integer = Int32.Parse(names(((iter * 4) + 3)), CultureInfo.InvariantCulture)
                        If ((length = -1) AndAlso Not pp.Property.PropertyType.IsValueType) Then
                            pp.PropertyValue = Nothing
                            pp.IsDirty = False
                            pp.Deserialized = True
                        End If
                        If ((names(((iter * 4) + 1)) = "S") AndAlso ((startPos >= 0) _
                                    AndAlso ((length > 0) AndAlso (values.Length >= (startPos + length))))) Then
                            pp.SerializedValue = values.Substring(startPos, length)
                        End If
                        If ((names(((iter * 4) _
                                    + 1)) = "B") _
                                    AndAlso ((startPos >= 0) _
                                    AndAlso ((length > 0) _
                                    AndAlso (buf.Length _
                                    >= (startPos + length))))) Then
                            Dim buf2() As Byte = New Byte((length) - 1) {}
                            Buffer.BlockCopy(buf, startPos, buf2, 0, length)
                            pp.SerializedValue = buf2
                        End If
                    End If
                End If
                iter = (iter + 1)
            Next
        Catch Ex As System.Exception
            ' Eat exceptions
        End Try
    End Sub

    ' Remove CAS in sample: [SecurityPermission(SecurityAction.Assert, Flags = SecurityPermissionFlag.SerializationFormatter)]
    Private Shared Sub PrepareDataForSaving(ByRef allNames As String, ByRef allValues As String, ByRef buf() As Byte, ByVal binarySupported As Boolean, ByVal properties As SettingsPropertyValueCollection, ByVal userIsAuthenticated As Boolean)
        Dim names As StringBuilder = New StringBuilder
        Dim values As StringBuilder = New StringBuilder
        Dim ms As MemoryStream
        If binarySupported Then
            ms = New MemoryStream
        End If
        Try
            Try
                Dim anyItemsToSave As Boolean = False
                For Each pp As SettingsPropertyValue In properties
                    If pp.IsDirty Then
                        If Not userIsAuthenticated Then
                            Dim allowAnonymous As Boolean = CType(pp.Property.Attributes("AllowAnonymous"), Boolean)
                            If allowAnonymous = True Or userIsAuthenticated = True Then
                                anyItemsToSave = True
                                Exit For
                            End If
                        Else
                            anyItemsToSave = True
                            Exit For
                        End If
                    End If
                Next
                If Not anyItemsToSave Then
                    Return
                End If
                Dim sStringToAppend As String = String.Empty
                For Each pp As SettingsPropertyValue In properties
                    If CType(pp.Property.Attributes("AllowAnonymous"), Boolean) = True Or userIsAuthenticated = True Then
                        If (pp.IsDirty AndAlso pp.UsingDefaultValue = False) Then
                            Dim startPos As Integer = 0
                            Dim len As Integer = 0
                            Dim propValue As String = Nothing
                            If (pp.Deserialized _
                                        AndAlso (pp.PropertyValue Is Nothing)) Then
                                len = -1
                            Else
                                Dim sVal As Object = pp.SerializedValue
                                If (sVal Is Nothing) Then
                                    len = -1
                                Else
                                    If (Not (TypeOf sVal Is String) _
                                                AndAlso Not binarySupported) Then
                                        sVal = Convert.ToBase64String(CType(sVal, Byte()))
                                    End If
                                    If (TypeOf sVal Is String) Then
                                        propValue = CType(sVal, String)
                                        len = propValue.Length
                                        startPos = values.Length
                                    Else
                                        Dim b2() As Byte = CType(sVal, Byte())
                                        startPos = CType(ms.Position, Integer)
                                        ms.Write(b2, 0, b2.Length)
                                        ms.Position = (startPos + b2.Length)
                                        len = b2.Length
                                    End If
                                End If
                            End If
                            sStringToAppend = pp.Name & ":"
                            If propValue Is Nothing Then
                                sStringToAppend = sStringToAppend & "B" & ":"
                            Else
                                sStringToAppend = sStringToAppend & "S" & ":"
                            End If
                            sStringToAppend = sStringToAppend & _
                            startPos.ToString(CultureInfo.InvariantCulture) & ":" & _
                            len.ToString(CultureInfo.InvariantCulture) & ":"
                            names.Append(sStringToAppend)

                            If (Not (propValue) Is Nothing) Then
                                values.Append(propValue)
                            End If
                        End If
                    End If
                Next
                If binarySupported Then
                    buf = ms.ToArray
                End If
            Finally
                If Not ms Is Nothing Then
                    ms.Close()
                End If
            End Try
        Catch Ex As System.Exception
            Throw
        End Try
        allNames = names.ToString
        allValues = values.ToString
    End Sub

    Private Function GetProfilesForQuery(ByVal authenticationOption As ProfileAuthenticationOption, _
    ByVal pageIndex As Integer, ByVal pageSize As Integer, ByRef totalRecords As Integer, _
    Optional ByVal UserNameToMatch As String = "", _
    Optional ByVal InactiveSince As DateTime = #1/1/1754#) As ProfileInfoCollection

        If (pageIndex < 0) Then
            Throw New ArgumentException("PageIndex bad, " & pageIndex)
        End If
        If (pageSize < 1) Then
            Throw New ArgumentException("PageSize ba), " & pageSize)
        End If
        Dim upperBound As Long = ((CType(pageIndex, Long) * pageSize) _
                    + (pageSize - 1))
        If (upperBound > Int32.MaxValue) Then
            Throw New ArgumentException("PageIndex PageSize bad, " & pageIndex & " and " & pageSize)
        End If
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Try
            Dim result As New Generic.List(Of Common.DbDataRecord)
            CheckSchemaVersion()
            Dim sSQL As String
            Dim deleteAnonUsers = False
            Dim htTempTable As New Hashtable
            sSQL = "SELECT u.UserName, u.IsAnonymous, u.LastActivityDate, p.LastUpdatedDate," & _
            "p.PropertyNames + p.PropertyValuesString + p.PropertyValuesBinary " & _
            "FROM " & _TablePrefix & "_Users u, " & _TablePrefix & "_Profile p " & _
            "WHERE u.ApplicationId = ? " & _
            "AND u.UserId = p.UserId "
            DAP.AddParameter("@ApplicationId", _AppId, DbType.String)
            Select Case authenticationOption
                Case ProfileAuthenticationOption.Anonymous
                    sSQL = sSQL & "AND u.IsAnonymous = ? "
                    DAP.AddParameter("@IsAnonymous", True, DbType.Int16)
                Case ProfileAuthenticationOption.Authenticated
                    sSQL = sSQL & "AND u.IsAnonymous = ? "
                    DAP.AddParameter("@IsAnonymous", False, DbType.Int16)
            End Select
            If String.IsNullOrEmpty(UserNameToMatch) Then
                sSQL = sSQL & "AND u.LoweredUserName = ? "
                DAP.AddParameter("@LoweredUserName", UserNameToMatch.ToLower, DbType.String)
            End If
            If InactiveSince <> #1/1/1754# Then
                sSQL = sSQL & "AND (u.LastActivityDate IS NULL OR u.LastActivityDate > ?) "
                DAP.AddParameter("@LastActivityDate", InactiveSince.ToUniversalTime, DbType.DateTime)
            End If
            result = DAP.ExecuteReader(CommandBehavior.SequentialAccess)
            Dim profiles As ProfileInfoCollection = New ProfileInfoCollection
            For Each Reader As Common.DbDataRecord In result
                Dim username As String
                Dim dtLastUpdated As DateTime
                Dim dtLastActivity As DateTime
                Dim isAnon As Boolean
                username = Reader.GetString(0)
                isAnon = Reader.GetBoolean(1)
                dtLastActivity = DateTime.SpecifyKind(Reader.GetDateTime(2), DateTimeKind.Utc)
                dtLastUpdated = DateTime.SpecifyKind(Reader.GetDateTime(3), DateTimeKind.Utc)
                Dim size As Integer = Reader.GetString(4).Length + Reader.GetString(5).Length + Reader.GetString(6).Length
                profiles.Add(New ProfileInfo(username, isAnon, dtLastActivity, dtLastUpdated, size))
            Next
            totalRecords = profiles.Count
            Return profiles
        Catch Ex As System.Exception
            Throw
        Finally
            DAP.Dispose()
        End Try
    End Function

#Region "Private Helper Methods"
    Private Sub CheckSchemaVersion()
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Dim features(0) As String
        features(0) = "Role Manager"

        Dim version As String = "1"
        If (features Is Nothing) Then
            Throw New ArgumentNullException("features")
        End If
        If (version Is Nothing) Then
            Throw New ArgumentNullException("version")
        End If
        Dim iStatus As Integer = 0
        For Each feature As String In features
            Dim sSQL As String = "SELECT COUNT(*) As SqlCount " & _
            "FROM " & _TablePrefix & "_SchemaVersions " & _
            "WHERE Feature = ? " & _
            "AND CompatibleSchemaVersion = ?"
            DAP.AddParameter("@Feature", feature, DbType.String)
            DAP.AddParameter("@CompatibleSchemaVersion", version, DbType.Int16)
            iStatus = DAP.ExecuteScalar(sSQL)
            If (iStatus = 0) Then
                _SchemaVersionCheck = -1
                Throw New ProviderException("Provider Schema Version Not Match " & MyBase.ToString & " version")
            End If
        Next
        If (_SchemaVersionCheck = -1) Then
            Throw New ProviderException("Provider Schema Version Not Match " & MyBase.ToString & " " & version)
        ElseIf (_SchemaVersionCheck = 0) Then
            If (_SchemaVersionCheck = -1) Then
                Throw New ProviderException("Provider Schema Version Not Match " & MyBase.ToString & " " & version)
            ElseIf (_SchemaVersionCheck = 0) Then
                _SchemaVersionCheck = 1
            End If
        End If
        DAP.Dispose()
    End Sub

    Private Function GetConfigValue(ByVal configValue As String, ByVal defaultValue As String) As String
        If String.IsNullOrEmpty(configValue) Then
            Return defaultValue
        End If
        Return configValue
    End Function

    Private Shared Function GetDefaultAppName() As String
        Try
            Dim appName As String = System.Web.Hosting.HostingEnvironment.ApplicationVirtualPath
            If String.IsNullOrEmpty(appName) Then
                appName = System.Diagnostics.Process.GetCurrentProcess.MainModule.ModuleName
                Dim indexOfDot As Integer = appName.IndexOf(ChrW(46))
                If (indexOfDot <> -1) Then
                    appName = appName.Remove(indexOfDot)
                End If
            End If
            If String.IsNullOrEmpty(appName) Then
                Return "/"
            Else
                Return appName
            End If
        Catch Ex As System.Exception
            Return "/"
        End Try
    End Function

    Friend Shared Function ValidateParameter(ByRef param As String, ByVal checkForNull As Boolean, ByVal checkIfEmpty As Boolean, ByVal checkForCommas As Boolean, ByVal maxSize As Integer) As Boolean
        If (param Is Nothing) Then
            Return Not checkForNull
        End If
        param = param.Trim
        If ((checkIfEmpty _
                    AndAlso (param.Length < 1)) _
                    OrElse (((maxSize > 0) _
                    AndAlso (param.Length > maxSize)) _
                    OrElse (checkForCommas AndAlso param.Contains(",")))) Then
            Return False
        End If
        Return True
    End Function

    Friend Shared Sub CheckParameter(ByRef param As String, ByVal checkForNull As Boolean, ByVal checkIfEmpty As Boolean, ByVal checkForCommas As Boolean, ByVal maxSize As Integer, ByVal paramName As String)
        If (param Is Nothing) Then
            If checkForNull Then
                Throw New ArgumentNullException(paramName)
            End If
            Return
        End If
        param = param.Trim
        If (checkIfEmpty _
                    AndAlso (param.Length < 1)) Then
            Throw New ArgumentException("Parameter can not be empty, " & paramName)
        End If
        If ((maxSize > 0) _
                    AndAlso (param.Length > maxSize)) Then
            Throw New ArgumentException("Parameter too long " & paramName & " " & maxSize.ToString(CultureInfo.InvariantCulture) & " " & paramName)
        End If
        If (checkForCommas AndAlso param.Contains(",")) Then
            Throw New ArgumentException("Parameter can not contain comma, " & paramName)
        End If
    End Sub

    Private Function GetAppId() As String
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        'Check to see if the application exists
        Dim sSQL As String = "SELECT ApplicationId FROM " & _TablePrefix & "_Applications WHERE LoweredApplicationName = ?"
        DAP.AddParameter("@ApplicationName", _ApplicationName, DbType.String)
        _AppId = DAP.ExecuteScalar(sSQL)
        'If it doesn't insert a new application into the _Applications table

        If String.IsNullOrEmpty(_AppId) Then
            Dim sNewAppId As String = System.Guid.NewGuid.ToString
            sSQL = "INSERT INTO " & _TablePrefix & "_Applications(ApplicationId, ApplicationName, LoweredApplicationName) " & _
            "VALUES(?, ?, ?)"
            DAP.AddParameter("@ApplicationId", sNewAppId, DbType.String)
            DAP.AddParameter("@ApplicationName", _ApplicationName, DbType.String)
            DAP.AddParameter("@LoweredApplicationName", _ApplicationName.ToLower, DbType.String)
            If DAP.ExecuteNonQuery(sSQL) = 1 Then
                _AppId = sNewAppId
            Else
                _AppId = Nothing
            End If
        End If
        Return _AppId
    End Function

    Friend Shared Sub CheckArrayParameter(ByRef param() As String, ByVal checkForNull As Boolean, ByVal checkIfEmpty As Boolean, ByVal checkForCommas As Boolean, ByVal maxSize As Integer, ByVal paramName As String)
        If (param Is Nothing) Then
            Throw New ArgumentNullException(paramName)
        End If
        If (param.Length < 1) Then
            Throw New ArgumentException("Parameter array empty, " & paramName)
        End If
        Dim values As Hashtable = New Hashtable(param.Length)
        Dim i As Integer = (param.Length - 1)
        Do While (i >= 0)
            CheckParameter(param(i), checkForNull, checkIfEmpty, checkForCommas, maxSize, (paramName + ("[ " _
                            + (i.ToString(CultureInfo.InvariantCulture) + " ]"))))
            If values.Contains(param(i)) Then
                Throw New ArgumentException("Parameter duplicate array element, " & paramName)
            Else
                values.Add(param(i), param(i))
            End If
            i = (i - 1)
        Loop
    End Sub
#End Region

#Region "Converted Procedures"
    Private Function Profile_GetProperties(ByVal Username As String, ByVal CurrentTimeUtc As DateTime) As Generic.List(Of Common.DbDataRecord)
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Try
            Dim returnVal As New Generic.List(Of Common.DbDataRecord)
            Dim sSQL As String
            Dim UserId As String = String.Empty
            sSQL = "SELECT UserId " & _
            "FROM " & _TablePrefix & "_Users " & _
            "WHERE  ApplicationId = ? AND LoweredUserName = ?"
            DAP.AddParameter("@ApplicationId", _AppId, DbType.String)
            DAP.AddParameter("@LoweredUserName", Username.ToLower, DbType.String)
            UserId = DAP.ExecuteScalar(sSQL)
            If String.IsNullOrEmpty(UserId) = False Then
                sSQL = "SELECT TOP 1 PropertyNames, PropertyValuesString, PropertyValuesBinary " & _
                "FROM " & _TablePrefix & "_Profile " & _
                "WHERE UserId = ?"
                DAP.AddParameter("@UserId", UserId, DbType.String)
                returnVal = DAP.ExecuteReader(sSQL)
            End If
            Return returnVal
        Finally
            DAP.Dispose()
        End Try
    End Function
#End Region
End Class

